# -*- encoding:utf-8 -*-
u"""同步信息系统."""


import json
import warnings
import urllib3
from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor

from config import config
from lib import auth
from lib.table import table
from lib.validform import V, ValidateForm
from lib.funcs import validateAnguan
urllib3.disable_warnings()
warnings.filterwarnings("ignore")

app = Blueprint(__name__ + "_app", __name__)


@app.before_request
def setupdb():
    u"""构造函数."""
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    u"""析构函数."""
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


@app.route('/api/sync/information/new_list_add', methods=['POST'])
@auth.permission('informationSystem')
def get_newList_add(_currUser):
    u"""查询同步网站变更详情新增."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_information_system' and e.operating = 'add' and e.person_id = '%s' and e.state = '1'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
             from(SELECT a.system_id as asystem_id, a.system_name_change as asystem_name_change, 
             a.system_desc_change as asystem_desc_change,
             a.sys_code_change as asys_code_change, 
             a.url_change as aurl_change, 
             a.agency_name_change as aagency_name_change, 
             d2t(e.r_time) as er_time 
             from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s ) a """ % (tb.where(['a.system_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT 
        a.system_id as asystem_id, 
        a.system_name_change as asystem_name_change, 
        a.system_desc_change as asystem_desc_change,
        a.sys_code_change as asys_code_change, 
        a.url_change as aurl_change, 
        a.agency_name_change as aagency_name_change, 
        d2t(e.r_time) as er_time 
        from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s %s %s %s """ % (tb.where(['a.system_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


@app.route('/api/sync/information/new_list_edit', methods=['POST'])
@auth.permission('informationSystem')
def get_newList_edit(_currUser):
    u"""查询同步网站变更详情更改."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_information_system' and e.operating = 'update' and e.person_id = '%s' and e.state = '1'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
             from(SELECT a.system_id as asystem_id, a.system_name_change as asystem_name_change, 
             a.system_desc_change as asystem_desc_change,
             a.sys_code_change as asys_code_change, 
             a.url_change as aurl_change, 
             a.agency_name_change as aagency_name_change, 
             d2t(e.r_time) as er_time 
             from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s ) a """ % (tb.where(['a.system_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT 
        a.system_id as asystem_id, 
        a.system_name_change as asystem_name_change, 
        a.system_desc_change as asystem_desc_change,
        a.sys_code_change as asys_code_change, 
        a.url_change as aurl_change, 
        a.agency_name_change as aagency_name_change, 
        d2t(e.r_time) as er_time 
        from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s %s %s %s """ % (tb.where(['a.system_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


@app.route('/api/sync/information/new_list_delete', methods=['POST'])
@auth.permission('informationSystem')
def get_newList_del(_currUser):
    u"""查询同步网站变更详情删除."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_information_system' and e.operating = 'delete' and e.person_id = '%s' and e.state = '1'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
             from(SELECT a.system_id as asystem_id, a.system_name_change as asystem_name_change, 
             a.system_desc_change as asystem_desc_change,
             a.sys_code_change as asys_code_change, 
             a.url_change as aurl_change, 
             a.agency_name_change as aagency_name_change, 
             d2t(e.r_time) as er_time 
             from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s ) a """ % (tb.where(['a.system_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT 
        a.system_id as asystem_id, 
        a.system_name_change as asystem_name_change, 
        a.system_desc_change as asystem_desc_change,
        a.sys_code_change as asys_code_change, 
        a.url_change as aurl_change, 
        a.agency_name_change as aagency_name_change, 
        d2t(e.r_time) as er_time 
        from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s %s %s %s """ % (tb.where(['a.system_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


@app.route('/api/sync/information/list_add', methods=['POST'])
@auth.permission('informationSystem')
def get_List_add(_currUser):
    u"""查询历史网站变更详情新增."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_information_system' and e.operating = 'add' and e.person_id = '%s' and e.state = '0'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
             from(SELECT a.system_id as asystem_id, a.system_name_change as asystem_name_change, 
             a.system_desc_change as asystem_desc_change,
             a.sys_code_change as asys_code_change, 
             a.url_change as aurl_change, 
             a.agency_name_change as aagency_name_change, 
             d2t(e.r_time) as er_time 
             from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s ) a """ % (tb.where(['a.system_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT 
        a.system_id as asystem_id, 
        a.system_name_change as asystem_name_change, 
        a.system_desc_change as asystem_desc_change,
        a.sys_code_change as asys_code_change, 
        a.url_change as aurl_change, 
        a.agency_name_change as aagency_name_change, 
        d2t(e.r_time) as er_time 
        from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s %s %s %s """ % (tb.where(['a.system_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


@app.route('/api/sync/information/list_update', methods=['POST'])
@auth.permission('informationSystem')
def get_List_edit(_currUser):
    u"""查询历史网站变更详情更改."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_information_system' and e.operating = 'update' and e.person_id = '%s' and e.state = '0'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
             from(SELECT a.system_id as asystem_id, a.system_name_change as asystem_name_change, 
             a.system_desc_change as asystem_desc_change,
             a.sys_code_change as asys_code_change, 
             a.url_change as aurl_change, 
             a.agency_name_change as aagency_name_change, 
             d2t(e.r_time) as er_time 
             from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s ) a """ % (tb.where(['a.system_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT 
        a.system_id as asystem_id, 
        a.system_name_change as asystem_name_change, 
        a.system_desc_change as asystem_desc_change,
        a.sys_code_change as asys_code_change, 
        a.url_change as aurl_change, 
        a.agency_name_change as aagency_name_change, 
        d2t(e.r_time) as er_time 
        from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s %s %s %s """ % (tb.where(['a.system_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


@app.route('/api/sync/information/list_delete', methods=['POST'])
@auth.permission('informationSystem')
def get_List_del(_currUser):
    u"""查询历史网站变更详情删除."""
    try:
        tb = table()
        _where = """e.synchroniz_table = 'h_information_system' and e.operating = 'delete' and e.person_id = '%s' and e.state = '0'""" % (_currUser['user']['person_id'])
        g.cursor.execute("""SELECT count(1) as num
             from(SELECT a.system_id as asystem_id, a.system_name_change as asystem_name_change, 
             a.system_desc_change as asystem_desc_change,
             a.sys_code_change as asys_code_change, 
             a.url_change as aurl_change, 
             a.agency_name_change as aagency_name_change, 
             d2t(e.r_time) as er_time 
             from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s ) a """ % (tb.where(['a.system_name_change'], _where)))
        total = g.cursor.fetchone()['num']
        g.cursor.execute("""SELECT 
        a.system_id as asystem_id, 
        a.system_name_change as asystem_name_change, 
        a.system_desc_change as asystem_desc_change,
        a.sys_code_change as asys_code_change, 
        a.url_change as aurl_change, 
        a.agency_name_change as aagency_name_change, 
        d2t(e.r_time) as er_time 
        from h_synchroniz_info e
        left join h_system_synchroniz_info a on e.r_time = a.r_time %s %s %s %s """ % (tb.where(['a.system_name_change'], _where), tb.orderBy(), tb.offset(), tb.limit()))
        dataSource = g.cursor.fetchall()
        return json.dumps({
            "status": "success",
            "data": {
                "current": tb.current(),
                "total": total,
                "dataSource": dataSource
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


@app.route('/api/sync/information/delete_list', methods=['POST'])
@auth.permission('informationSystem')
def delete_important_list(_currUser):
    u"""同步信息标记已读"""
    try:
        g.cursor.execute("""update h_synchroniz_info set state = '0' where synchroniz_table = 'h_information_system' and person_id = '%s' """ % (_currUser['user']['person_id']))
        g.conn.commit()
        return json.dumps({
            "status": "success"
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '异常'})


# 同步新增数据
@app.route('/api/sync/information/add', methods=['POST'])
def add_website():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            info_sys_id=['信息系统编号', V.required()],
            info_sys_name=['信息系统名称', V.required(), V.maxlength(100)],
            info_sys_description=['信息系统描述', V.optional(), V.maxlength(512)],
            info_sys_code=["信息系统代码", V.required(), V.maxlength(50)],
            url=["url地址", V.optional(), V.url()],
            tag=["标签", V.optional(), V.maxlength(50)],
            maintainer=["维护人", V.optional(), V.maxlength(30)],
            maintainer_phone=["维护人电话", V.optional(), V.pattern('^1[0-9]{10}$')],
            maintainer_email=["维护人邮箱", V.optional(), V.email()],
            remark=["备注", V.optional(), V.maxlength(512)],
            node_id=['机构编号', V.required(), V.maxlength(30)],
            r_person=['当前用户', V.required(), V.maxlength(30)]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E1'
            data['msg'] = {'errorKey': data.pop('_key'), 'message': data['msg']}
            return json.dumps(data)
        g.cursor.execute("""select count(info_sys_id) as num from h_information_system where info_sys_id=%(info_sys_id)s and state='1'""", (data))
        num = g.cursor.fetchone()['num']
        if num > 0:
            return json.dumps({"status": "E2", "msg": {'errorKey': 'info_sys_id', 'message': '此条信息系统已存在'}})
        if data["url"]:
            g.cursor.execute("""select count(url) as num from h_information_system where url=%(url)s and state='1'""", (data))
            num = g.cursor.fetchone()['num']
            if num > 0:
                return json.dumps({"status": "E2", "msg": {'errorKey': 'info_sys_id', 'message': '此条URL已存在'}})
        g.cursor.execute("""select agency_name from sys_agency_info where agency_id=%(node_id)s and state='1'""", (data))
        agency_name = g.cursor.fetchone()
        if agency_name is None:
            return json.dumps({"status": "E2", "msg": {'errorKey': 'agency_name', 'message': '机构不存在'}})
        g.cursor.execute(
            """insert into h_information_system (
                agency_id, 
                state, 
                info_sys_id, 
                info_sys_name, 
                info_sys_description, 
                info_sys_code,
                url, 
                tag, 
                maintainer, 
                maintainer_phone,
                maintainer_email, 
                remark, 
                data_source, 
                update_time) 
                values (
                 %(node_id)s,
                '1',
                %(info_sys_id)s, 
                %(info_sys_name)s,
                %(info_sys_description)s,
                %(info_sys_code)s,
                %(url)s,
                %(tag)s,
                %(maintainer)s,
                %(maintainer_phone)s,
                %(maintainer_email)s,
                %(remark)s,
                '0',
                now())
            """, (data))
        g.cursor.execute("""INSERT into h_system_synchroniz_info (system_id,system_name_change,system_desc_change,sys_code_change,url_change,tag_change_info,maintainer_change_info,maintainer_phone_change_info,maintainer_email_change_info,remark_change,agency_name_change) values (%(info_sys_id)s,%(info_sys_name)s,%(info_sys_description)s,%(info_sys_code)s,%(url)s,%(tag)s,%(maintainer)s,%(maintainer_phone)s,%(maintainer_email)s,%(remark)s,(select agency_name from sys_agency_info where agency_id = %(node_id)s and state='1'))""", (data))
        g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception,e:
        return json.dumps({"status": "E0", 'msg': '异常', "warn": str(e)})


# 同步更改数据
@app.route('/api/sync/information/update', methods=['POST'])
def update_website():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            info_sys_id=['信息系统编号', V.required()],
            info_sys_name=['信息系统名称', V.required(), V.maxlength(100)],
            info_sys_description=['信息系统描述', V.optional(), V.maxlength(512)],
            info_sys_code=["信息系统代码", V.required(), V.maxlength(50)],
            url=["url地址", V.optional(), V.url()],
            tag=["标签", V.optional(), V.maxlength(50)],
            maintainer=["维护人", V.optional(), V.maxlength(30)],
            maintainer_phone=["维护人电话", V.optional(), V.pattern('^1[0-9]{10}$')],
            maintainer_email=["维护人邮箱", V.optional(), V.email()],
            remark=["备注", V.optional(), V.maxlength(512)],
            node_id=['机构编号', V.required(), V.maxlength(30)],
            r_person=['当前用户', V.required(), V.maxlength(30)]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E1'
            data['msg'] = {'errorKey': data.pop('_key'), 'message': data['msg']}
            return json.dumps(data)
        # g.cursor.execute("""select info_sys_id from h_information_system where info_sys_id=%(info_sys_id)s and state='1'""")
        g.cursor.execute("""select info_sys_name from h_information_system where info_sys_name=%(info_sys_name)s and state='1' and info_sys_id != %(info_sys_id)s""", (data))
        info_sys_name = g.cursor.fetchone()
        if info_sys_name is not None:
            return json.dumps({
                "status": "E2",
                "msg": {'errorKey': 'info_sys_name', 'message': '此条信息系统已存在'}
            })
        if data["url"]:
            g.cursor.execute("""select url from h_information_system where url=%(url)s and state='1' and info_sys_id != %(info_sys_id)s""", (data))
            url = g.cursor.fetchone()
            if url is not None:
                return json.dumps({
                    "status": "E2",
                    "msg": {'errorKey': 'URL', 'message': '此条URL已存在'}
                })
        g.cursor.execute("""select agency_id from sys_agency_info where agency_id=%(node_id)s and state='1'""", (data))
        agency_id = g.cursor.fetchone()
        if agency_id is None:
            return json.dumps({"status": "E2", "msg": {'errorKey': 'agency_id', 'message': '机构不存在'}})
        g.cursor.execute(
            """update h_information_system set 
            info_sys_name = %(info_sys_name)s,
            agency_id =%(node_id)s,
            info_sys_description = %(info_sys_description)s,
            info_sys_code = %(info_sys_code)s,
            url = %(url)s,
            tag = %(tag)s,
            maintainer = %(maintainer)s,
            maintainer_phone = %(maintainer_phone)s,
            maintainer_email = %(maintainer_email)s,
            remark = %(remark)s,
            update_time = now() 
            where info_sys_id = %(info_sys_id)s and state='1' """, (data))
        g.cursor.execute("""INSERT into h_system_synchroniz_info (system_id,system_name_change,system_desc_change,sys_code_change,url_change,tag_change_info,maintainer_change_info,maintainer_phone_change_info,maintainer_email_change_info,remark_change,agency_name_change)values(%(info_sys_id)s,%(info_sys_name)s,%(info_sys_description)s,%(info_sys_code)s,%(url)s,%(tag)s,%(maintainer)s,%(maintainer_phone)s,%(maintainer_email)s, %(remark)s,(select agency_name from sys_agency_info where agency_id = %(node_id)s))""", (data))
        g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception, e:
        return json.dumps({"status": "E0", 'msg': '异常', "warn": str(e)})


# 同步删除数据
@app.route('/api/sync/information/delete', methods=["POST"])
def delete():
    try:
        if validateAnguan()=='fail':
            return json.dumps({'status': 'E1','msg': '未通过安全校验'})
        form = ValidateForm(
            info_sys_id=["信息系统编号", V.required()]
        )
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'E1'
            data['msg'] = {'errorKey': data.pop('_key'), 'message': data['msg']}
            return json.dumps(data)
        g.cursor.execute(
            """update h_information_system set state = '0', update_time = now() where  info_sys_id = %(info_sys_id)s and state = '1' """, (data))
        g.cursor.execute("""INSERT into h_system_synchroniz_info (
            system_id,
            system_name_change,
            system_desc_change,
            sys_code_change,
            url_change,
            tag_change_info,
            maintainer_change_info,
            maintainer_phone_change_info,
            maintainer_email_change_info,
            remark_change,
            agency_name_change) select 
            a.info_sys_id as system_id,
            a.info_sys_name as system_name_change,
            a.info_sys_description as  system_desc_change,
            a.info_sys_code as sys_code_change,
            a.url as url_change,
            a.tag as tag_change_info,
            a.maintainer as maintainer_change_info,
            a.maintainer_phone as maintainer_phone_change_info,
            a.maintainer_email as maintainer_email_change_info,
            a.remark as remark_change,
            b.agency_name as agency_name_change from h_information_system a 
            left join sys_agency_info b on a.agency_id = b.agency_id
            where a.info_sys_id = %(info_sys_id)s
            """, (data))
        g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception:
        return json.dumps({"status": "E0", 'msg': '异常'})


 
